Pour exporter en slides, lancer :
jupyter nbconvert Bases\ de\ données.ipynb --to slides --post serve
In [1]:
-- connection: dbname=cours user=cours
Dans le cadre de ce cours, on se concentrera sur les SGBD.
Pourquoi éviter ce terme abusif ?
Au même titre, Twitter, les services Google, et presque tout l’Internet moderne pourrait être nommé « base de données ».
Même un tableur type Excel ou une feuille de papier peuvent être appelés abusivement « base de données ».
Pas faux en soit, mais peu précis.
une base de données ≠ un Système de Gestion de Base de Données
un SGBD = logiciel
une base de données = contenu
Dans l’ordre d’utilisation, on :
Excel : non structuré, ne permet pas de faire de requêtes au-delà de filtrages basiques
Gallica : contient une base de données mais n’est pas un SGBD (pas de création de nouvelle base de données au sein de Gallica)
Papier : non structuré, ne permet pas de faire de requêtes.
En plus de devoir gérer les actions listées précédemment, un SGBD doit permettre de réaliser toutes ces tâches sous forme de code informatique, et être ainsi totalement automatisables. Cela permet d’intégrer les données dans n’importe quel programme : application web, de bureau, mobile.
Une base de données (ou database, DB, BDD) est composée de tables stockant des lignes suivant plusieurs colonnes.
Chaque table contient les données structurées suivant les mêmes colonnes.
Par exemple, la base de données « bibliothèque » contient les tables « livres », « auteurs », « genres », « collections », « éditeurs », etc.
Une ligne correspond à un ensemble de données représentant un seul objet.
Par exemple « Le Horla de Maupassant ».
Une colonne correspond à un champ. Par exemple « année de publication ».
Une valeur est la donnée élémentaire contenue dans une cellule, c’est-à-dire l’intersection d’une ligne et d’une colonne. Par exemple, l’année de publication du Horla de Maupassant est « 1886 ».
Ensuite, on lance une requête (sans lance-requêtes) pour demander à la base de données d’extraire des informations. Par exemple, on peut demander tous les livres de Maupassant, ou le nombre de livres fantastiques publiés entre 1880 et 1890.
La plupart du temps :
Le type de base de données indispensable, vu dans ce cours :
les bases de données relationnelles en SQL
Structured Query Language (= Langage de requêtes structurées)
Langage informatique créé en 1974 ayant abouti à une norme internationale
Un langage pour décrire la forme du résultat que l’on souhaite obtenir, et non le raisonnement.
SQL ≠ SGBD
SQL = langage
SGBD = logiciel pouvant être utilisé à l’aide d’un langage comme SQL
Célèbres SGBD où les requêtes s’écrivent en SQL :
| Nom | Prononciation idéale | Prononciation à la française |
|---|---|---|
| MySQL | maïèskyouèl | maïèsküèl |
| SQLite | sikoueulaïte | èsküèlite |
| Oracle | owreukeul | oracle |
| Microsoft SQL server | maïcrossoft èskyouèl seurveur | microssoft esküèl serveur |
| PostgreSQL | poste-graisse-kyouèl | poste-graisse-küèl |
Ce tableau est précieux, tout le monde se trompe sur ces prononciations.
Ouvrez un onglet de navigateur web sur NoriSQL : https://sql.noripyt.com
Trois parties :
Sous les deux champs permettant de remplir des requêtes s’affiche le résultat de ces requêtes, ou les erreurs contenues dans la requête.
À chaque modification puis lancement, l’URL de NoriSQL change : conservez chaque nouvelle URL pour chaque étape que vous souhaitez sauvegardez ! Créez un document que vous sauvegardez régulièrement contenant à chaque modification un descriptif de ce que vous venez de faire + l’URL.
Créer une base de données de livres, ceux que vous adorez plus ou moins.
Au fur et à mesure de l’avancée du cours, on va rencontrer des problèmes nécessitant d’affiner la structure de la base de données.
Nous allons tous collaborer à un fichier Google Drive servant de source à cette base de données :
https://docs.google.com/spreadsheets/d/1xptGD4t671SJrdRum18GsJh65dILbDvHp4bzwD2W--Q/edit?usp=sharing
Utiliser ce fichier de source collaboratif permettra de vous faire voir les problématiques de formatage des données, le problème numéro un auquel vous ferez face.
In [2]:
CREATE TABLE books (id int, title text, author text,
pages int, is_author_alive boolean);
Types de données élémentaires :
| Type SQL | Exemple de donnée | Traduction |
|---|---|---|
boolean |
true ou false |
Booléen, soit vrai ou faux |
int |
-723 |
Nombre entier |
float |
3.1415 |
Nombre à virgule avec précision incertaine (inadapté pour des prix) |
text |
'bonjour' |
Texte libre |
varchar(57) |
'salut' |
Texte libre, jusqu’à 57 caractères |
date |
DATE '2017-10-16' |
Une date |
time |
TIME '10:08:27' |
Une heure |
timestamp |
TIMESTAMP '2017-10-16 10:08:27' |
Un moment dans l’histoire, donc une combinaison de date et heure |
In [3]:
INSERT INTO books
VALUES (1, 'Alice au pays des merveilles', 'Lewis Carroll', 196, false),
(2, 'Les Piliers de la terre', 'Ken Follett', 1050, true);
Ajout en précisant les colonnes :
In [4]:
INSERT INTO books (id, title, author)
VALUES (3, 'De l’autre côté du miroir', 'Lewis Carroll'),
(4, 'Un Monde sans fin', 'Ken Follett');
In [5]:
SELECT * FROM books;
Sélection de deux colonnes :
In [6]:
SELECT title, author FROM books;
In [7]:
SELECT title AS titre, author AS auteur FROM books;
In [8]:
SELECT * FROM books LIMIT 3;
Pour sauter les premiers résultats :
In [9]:
SELECT * FROM books OFFSET 2;
In [10]:
SELECT * FROM books WHERE author = 'Lewis Carroll';
Sélection de tous les livres écrits par Ken Follett de plus de 1100 pages :
In [11]:
SELECT * FROM books WHERE author = 'Ken Follett' AND pages < 1100;
| Exemple | Traduction |
|---|---|
a = b |
a est égal à b |
a != b |
a est différent de b |
a > b |
a est supérieur à b |
a < b |
a est inférieur à b |
a >= b |
a est supérieur ou égal à b |
a <= b |
a est inférieur ou égal à b |
a IS NULL |
a n’a pas été rempli (tout type sauf texte) |
a IN (1, 2, 3) |
a est parmi les valeurs 1, 2 ou 3 |
a LIKE '%feuill__' |
a vaut 'feuillue' ou 'portefeuilles' |
Combinaison de comparaisons :
| Exemple | Traduction |
|---|---|
a AND b |
true si à la fois a et b sont true |
a OR b |
true si soit a ou b est true, ou les deux |
NOT a |
true si a est false |
Écrire une requête récupérant les livres dont le titre commence par « Le ».
Écrire une requête récupérant les livres dont l’auteur est toujours vivant et a un nom contenant un « e ».
Écrire une requête récupérant les livres dont l’un des deux champs suivants (ou les deux) n’est pas défini : pages ou is_author_alive
In [12]:
INSERT INTO books (title) VALUES ('La Chute des géants');
Oups, j’ai oublié quelques infos !
In [13]:
UPDATE books
SET id = 5, author = 'Ken Follett'
WHERE title = 'La Chute des géants';
Est-ce que ça a marché ?
In [14]:
SELECT * FROM books WHERE author = 'Ken Follett';
Oui !
In [15]:
ALTER TABLE books ADD COLUMN publication_year int;
In [16]:
ALTER TABLE books DROP COLUMN publication_year;
Mettre une valeur par défaut :
In [17]:
ALTER TABLE books ALTER COLUMN title SET DEFAULT '[Titre inconnu]';
Mais ce qui nous intéresse, c’est de rendre des champs obligatoires :
In [18]:
ALTER TABLE books ALTER COLUMN title SET NOT NULL,
ALTER COLUMN author SET NOT NULL;
Impossible désormais de ne pas remplir les champs titre et auteur !
Liste complète des possibilités de modification de structure : https://www.postgresql.org/docs/10/static/sql-altertable.html
In [19]:
ALTER TABLE books ALTER COLUMN id SET NOT NULL;
ALTER TABLE books ALTER COLUMN is_author_alive SET DEFAULT false;
In [ ]:
INSERT INTO books (title, author)
VALUES ('Alice in Wonderland', 'Lewis Carroll');
Oups, un doublon !
Retirons-le :
In [21]:
DELETE FROM books
WHERE title = 'Alice in Wonderland';
Et si le titre avait été le même ?
Définir des identifiants soi-même, tâche rébarbative et source d’erreurs.
Les identifiants auto-incrémentés, ou numéros de série, sont là pour ça.
À chaque nouvel ajout dans la base de données, nouvel identifiant créé.
Très important ! L’identifiant d’une donnée supprimée n’est jamais réutilisé.
Pour créer un identifiant auto-incrémenté, on dispose du type serial.
Supprimer la table.
Recréer la table en changeant l’identifiant de books en un identifiant auto-incrémenté.
Ne pas réimporter les données.
Insérer une ligne sans préciser id. Sélectionner toutes les lignes pour vérifier si id s’est rempli automatiquement.
Note : pour cette modification de structure importante et uniquement celle-là, il est difficile (mais possible) de le faire sans supprimer la table. C’est pourquoi on s’autorise ici à ne pas faire de commande ALTER TABLE.
In [22]:
DROP TABLE books;
CREATE TABLE books (id serial, title text NOT NULL, author text, pages int, is_author_alive boolean);
INSERT INTO books (title, author, pages, is_author_alive)
VALUES ('Alice au pays des merveilles', 'Lewis Carroll', 196, false),
('Les Piliers de la terre', 'Ken Follett', 1050, true),
('De l’autre côté du miroir', 'Lewis Carroll', NULL, NULL),
('Un Monde sans fin', 'Ken Follett', NULL, NULL),
('La Chute des géants', 'Ken Follett', NULL, NULL);
In [23]:
INSERT INTO books (id, title, author)
VALUES (5, 'L’Hiver du monde', 'Ken Follett');
In [24]:
SELECT * FROM books WHERE id = 5;
In [25]:
DELETE FROM books WHERE id = 5 AND title = 'L’Hiver du monde';
On peut marquer une colonne comme étant clé primaire, ou primary key. Ainsi, elle ne peut avoir deux fois la même valeur.
De plus, la clé primaire est la colonne officiellement utilisée comme identifiant par la base de données pour cette table.
On peut donc faire une clé primaire sur n’importe quelle colonne de n’importe quel type, tant que les données ne sont pas jamais en double. Par exemple :
In [26]:
CREATE TABLE countries (name varchar(100) PRIMARY KEY);
INSERT INTO countries VALUES ('France'), ('Belgique'), ('Italie');
In [27]:
SELECT * FROM countries;
Toutefois, il est désormais impossible d’avoir un pays en doublon.
Dans le cas des livres, utiliser le titre comme identifiant est ambigu: si on vous parle de Confessions, pouvez-vous savoir de quel livre précis il s’agit ? Il y en a tellement eu, c’est impossible.
Mais dans le cas des pays, pas d’ambigüité: la liste est courte et stable, il ne devrait jamais y avoir de confusion sur le pays dont il s’agit quand on donne son nom. Utiliser le nom comme identifiant au lieu d’un nombre est ici adapté.
Faire votre propre table contenant quelques pays.
Essayer d’insérer un doublon dans cette table pour constater ce qu’il se passe.
Supprimer la table des pays.
Trouver d’autres scénarios que celui vu dans la slide précédente pour « forcer » la création d’un doublon dans la table books.
Pistes :
INSERT INTO pour modifier les données de la table.id pour créer un doublon.Réécrire table de livres pour que la colonne id soit la clé primaire de la table.
Tenter à nouveau de créer des doublons de plusieurs manières différentes une fois la clé primaire créée.
In [28]:
DROP TABLE books;
CREATE TABLE books (id serial PRIMARY KEY, title text NOT NULL, author text, pages int, is_author_alive boolean);
INSERT INTO books (title, author, pages, is_author_alive)
VALUES ('Alice au pays des merveilles', 'Lewis Carroll', 196, false),
('Les Piliers de la terre', 'Ken Follett', 1050, true),
('De l’autre côté du miroir', 'Lewis Carroll', NULL, NULL),
('Un Monde sans fin', 'Ken Follett', NULL, NULL),
('La Chute des géants', 'Ken Follett', NULL, NULL);
SELECT * FROM books;
In [29]:
DROP TABLE countries;
Cette commande donne une erreur lorsque la table n’existe pas, ce qui est normal et souhaitable.
Toutefois, il existe un raccourci pour supprimer la table et ne pas déclencher d’erreur si elle n’existe pas :
In [30]:
DROP TABLE IF EXISTS countries;
In [31]:
SELECT count(*) FROM books;
Sélection du nombre total de pages dans la table :
In [32]:
SELECT sum(pages) FROM books;
Liste agrégations disponibles : https://www.postgresql.org/docs/10/static/functions-aggregate.html
Notamment min, max et avg.
Écrire une requête récupérant le nombre de livres écrits par votre auteur préféré.
Écrire une requête récupérant le nombre de livres contenant la syllabe tion dans le titre.
Écrire une requête calculant le nombre moyen de pages par livre.
Écrire une requête calculant le pourcentage de livres écrits par des auteurs vivants.
Indices :
count à la place de *. Il n’y a pas à mettre de WHERE.float dans une division de nombre entiers, il faut transformer au moins un des deux nombres en float, en faisant CAST(my_number AS float).
In [33]:
SELECT count(*) FROM books WHERE author = 'Ken Follett';
SELECT count(*) FROM books WHERE title = '%tion%';
SELECT avg(pages) FROM books;
SELECT 100 * CAST(count(CASE WHEN is_author_alive THEN true
ELSE NULL END)
AS float)
/ count(*) || ' %' AS alive_ratio
FROM books;
In [34]:
SELECT * FROM books ORDER BY title;
Ordonner les livres par ordre alphabétique croissant d’auteur, puis par ordre décroissant de titre :
In [35]:
SELECT * FROM books ORDER BY author ASC, title DESC;
« Ken Follett » devrait être après « Lewis Carroll », car F > C
Solution : séparer nom et prénom
Pour faciliter la recherche de titres, on ordonne généralement indépendamment du premier article.
« Les Piliers de la terre » devrait être après « Un Monde sans fin », car P > M
Solution : séparer l’article du titre
Modifier la structure de la table pour corriger ces deux problèmes.
Il faut maintenant séparer manuellement les données.
Dans le fichier Google Drive, ajuster la structure de vos livres préférés conformément à la nouvelle structure.
Réimporter ensuite les données du Google Drive dans la base de données SQL.
Écrire une requête sélectionnant à nouveau les données par ordre de titre pour constater qu’elles sont correctement ordonnées.
Réécrire la requête précédente pour que :
full_titleauthorL’opérateur pour concaténer les chaînes de caractères est ||.
In [36]:
DROP TABLE books;
CREATE TABLE books (id serial PRIMARY KEY, title_prefix text, title text NOT NULL,
author_first_name text, author_last_name text, pages int, is_author_alive boolean);
INSERT INTO books (title_prefix, title, author_first_name, author_last_name, pages, is_author_alive)
VALUES ('', 'Alice au pays des merveilles', 'Lewis', 'Carroll', 196, false),
('Les ', 'Piliers de la terre', 'Ken', 'Follett', 1050, true),
('', 'De l’autre côté du miroir', 'Lewis', 'Carroll', NULL, NULL),
('Un ', 'Monde sans fin', 'Ken', 'Follett', NULL, NULL),
('La ', 'Chute des géants', 'Ken', 'Follett', NULL, NULL);
In [37]:
SELECT id, title_prefix || title AS full_title, author_first_name || ' ' || author_last_name AS author, pages, is_author_alive
FROM books
ORDER BY title;
Dans notre table, un champ inadapté : is_author_alive
C’est une donnée qu’on peut déduire d’autres données : on déduit qu’un auteur est toujours vivant si nous n’avons pas trouvé sa date de décès.
La date de décès étant toujours plus précise que is_author_alive, on préfère stocker la date de décès, ce qui nous permettra de déduire si l’auteur est vivant.
De même, on pourrait souhaiter stocker l’âge de l’auteur.
Très mauvaise pratique ! donnée déduite des dates de naissance et décès, et relative au moment de la saisie.
En stockant dates de naissance et décès d’un auteur, on peut déduire beaucoup d’autres informations, comme :
Écrire une requête renvoyant uniquement les données suivantes :
Attention : pour calculer l’âge de l’auteur, vous avez besoin de ces outils :
age(death, birth). Si l’auteur n’est pas encore mort, alors il faut écrire la date d’aujourd’hui à la place.now().COALESCE(a, b, c, …). Si a est définie (donc ne vaut pas NULL), la fonction renverra a. Si a vaut NULL et b est définie, alors cette fonction renverra b. Si seule c est définie, alors la fonction renverra c, etc.EXTRACT(year from …), où on remplace les points de suspension par la données de laquelle extraire les années.
In [38]:
ALTER TABLE books ADD COLUMN author_birth date;
ALTER TABLE books ADD COLUMN author_death date;
ALTER TABLE books DROP COLUMN is_author_alive;
UPDATE books SET author_birth = '1949-6-5' WHERE author_last_name = 'Follett';
UPDATE books SET author_birth = '1832-1-27', author_death = '1898-1-14' WHERE author_last_name = 'Carroll';
In [39]:
SELECT author_first_name, author_last_name, author_death IS NULL AS is_author_alive,
EXTRACT(year from age(COALESCE(author_death, now()), author_birth)) AS age
FROM books;
In [40]:
SELECT DISTINCT author_first_name, author_last_name
FROM books
ORDER BY author_last_name;
Toutefois, les données restent en double dans la base de données.
En cas de modification d’orthographe d’un nom d’auteur, ou de sa date de naissance, il faut modifier tous les livres de cet auteur !
La solution est de séparer les données des auteurs des données de livres, en gardant un lien entre les deux.
Créer une nouvelle feuille dans le tableur pour contenir la table des auteurs.
Y recopier les données de vos auteurs préférés, telles que saisies dans la table des livres.
Attention, ne pas supprimer les données de la table des livres pour le moment
Exporter en TSV les deux feuilles, de sorte à créer la table authors et recréer une fois de plus la table books.
Ne pas oublier de corriger la structure de table générée par NoriSQL, c’est-à-dire faire attention aux champs obligatoires, clés primaires et types de colonnes.
In [41]:
CREATE TABLE authors (
id serial PRIMARY KEY,
first_name varchar(50),
last_name varchar(50) NOT NULL,
birth date NOT NULL,
death date
);
INSERT INTO authors
VALUES (1, 'Lewis', 'Carroll', '1832-1-27', '1898-1-14'),
(2, 'Ken', 'Follett', '1949-6-5', NULL);
SELECT * FROM authors;
Maintenant que nous avons déplacé des données dans la table authors, il faut faire un lien avec la table books et supprimer les données d’auteurs de la table des livres.
Pour créer un lien entre deux tables, on stocke sur chaque ligne d’une une des deux tables la clé primaire d’une ligne de l’autre.
Exemple de table de livres :
| id | title | author_id |
|---|---|---|
| 1 | Alice au Pays des merveilles | 1 |
| 2 | De l’autre côté du miroir | 1 |
| 3 | Les Piliers de la terre | 2 |
| 4 | La Chute des géants | 2 |
Ainsi que la table d’auteurs correspondante :
| id | first_name | last_name |
|---|---|---|
| 1 | Lewis | Carroll |
| 2 | Ken | Follett |
On écrit une règle permettant d’assurer que la ligne de la table liée existera toujours, pour éviter des liens morts. Cette règle spéciale est appelée une contrainte.
On a déjà défini des genres de contraintes : on a forcé le format des données et on a rendu des champs obligatoires, par exemple. Ce sont des genre de contraintes de saisie, mais cela ne s’appelle pas des contraintes dans le jargon des bases de données. On y reviendra.
Toutefois, on a déjà créé une vraie contrainte de base de données de manière déguisée : une clé primaire créé automatiquement une contrainte d’unicité, notée UNIQUE, afin qu’il soit impossible de créer des doublons d’identifiants.
Cette contrainte que l’on va écrire pour lier les tables est appelée une clé étrangère, ou foreign key, et est notée FOREIGN KEY.
Pour créer cette contrainte :
In [42]:
CREATE TABLE main_table (id serial PRIMARY KEY, related_id int);
CREATE TABLE related_table (id int PRIMARY KEY);
In [43]:
ALTER TABLE main_table
ADD CONSTRAINT main_table_fk FOREIGN KEY (related_id)
REFERENCES related_table (id);
In [44]:
DROP TABLE main_table;
DROP TABLE related_table;
Dans le tableur, donner explicitement des identifiants uniques à chacun des auteurs que vous avez saisi.
Puis, saisir dans une colonne nommée author_id l’identifiant de chacun des auteurs que vous avez saisi.
Une fois que tout le monde a bien rempli la colonne author_id, une âme charitable se dévoue pour supprimer les colonnes des auteurs qu’on trouve dans la table des livres, hormis author_id bien sûr.
Importer à nouveau les deux tables.
Ne pas oublier de corriger la structure de table générée par NoriSQL, c’est-à-dire faire attention aux champs obligatoires, clés primaires et types de colonnes.
Attention aussi, author_id ne doit pas être serial ni PRIMARY KEY, puisqu’il ne s’agit pas de l’identifiant de la table en cours, books, mais de la table étrangère liée, authors.
Créer la contrainte FOREIGN KEY entre les deux tables. Il est possible que la contrainte refuse de se créer s’il y a des erreurs dans les données. Corriger les données le cas échéant.
Sans faire appel au tableur+import, créer manuellement un livre d’un auteur déjà existant.
Puis, créer un livre d’un auteur n’existant pas encore.
Une fois cela fait, recopier les deux nouveaux livres ainsi tapés dans la base de données, de sorte que chacun ait créé 5 livres.
In [45]:
ALTER TABLE books ADD COLUMN author_id int;
UPDATE books SET author_id = 1 WHERE author_last_name = 'Carroll';
UPDATE books SET author_id = 2 WHERE author_last_name = 'Follett';
ALTER TABLE books DROP COLUMN author_first_name;
ALTER TABLE books DROP COLUMN author_last_name;
ALTER TABLE books DROP COLUMN author_birth;
ALTER TABLE books DROP COLUMN author_death;
ALTER TABLE books
ADD CONSTRAINT books_author_fk FOREIGN KEY (author_id)
REFERENCES authors (id);
SELECT * FROM books INNER JOIN authors ON (books.author_id = authors.id);
Notre base de données est enfin mieux structurée ! Pour désigner la satisfaction qu’apporte une structure réussie, on dit que c’est une solution élégante.
Mais… Cela a beau être élégant, ce n’est pas pratique, on accède soit aux livres, soit aux auteurs, mais jamais aux deux en même temps :
In [46]:
SELECT * FROM books LIMIT 3;
In [47]:
SELECT * FROM authors;
Bien utile pour faire des efficacement des recherches sur les livres ou les auteurs, mais impossible de demander quels sont les noms des auteurs d’un ensemble de livres, ou les livres d’un auteur donné.
Jusqu’à présent, on a toujours sélectionné les données dans une seule table à l’aide de SELECT.
Maintenant, on veut sélectionner dans deux tables à la fois. Pour cela, on fait une jointure, JOIN, en aujoutant aux données principales des données annexes. On précise également par quelle règle on fait le rapprochement entre les deux tables :
In [48]:
SELECT * FROM books
JOIN authors ON authors.id = books.author_id
LIMIT 3;
ON fonctionne exactement comme WHERE, mais il se place uniquement après un JOIN.
On peut ajouter un WHERE après un JOIN pour filtrer les données.
Ici, pour la première fois on précise le nom des tables avec la syntaxe table.colonne afin d’éviter les ambigüités. En effet, les deux tables ayant une colonne id, la base de données ne saurait quelle colonne choisir entre ces deux homonymes.
Réécrire la requête joignant les deux tables, mais en n’affichant pas les deux colonnes (ici peu pertinentes) montrant l’identifiant de l’auteur.
Retirer également la limite.
Écrire une requête listant uniquement le titre de tous les livres écrits par l’auteur dont le nom de famille est Follett.
Écrire une requête listant les cinq livres écrits par les auteurs nés le plus récemment.
Un même livre peut avoir plusieurs titres en fonction de sa langue de traduction. D’autres données varient aussi avec la traduction : l’année de publication, le nombre de pages, l’éditeur, et même les auteurs ! En effet, on peut compter le traducteur dans les auteurs, mais laissons ce problème de côté car nous ne gérons qu’un auteur par livre.
Il existe de nombreuses méthodes pour gérer des traductions. On pourrait ajouter une nouvelle colonne original_title, mais cette donnée serait alors redondante entre les différentes traductions d’un même livre. On pourrait aussi séparer la colonne title en title_en, title_fr, title_de etc. Dans certains cas, ces techniques sont pertinentes, mais ici ce n’est pas le cas, car on peut avoir un livre dans des centaines de langues, or on ne va pas s’amuser à avoir des centaines de colonnes juste pour le titre. De plus, il reste le problème des autres données variant avec la langue, notamment le nombre de pages.
Voici la solution nous convenant : on va considérer qu’une traduction est un livre à part de l’original. Toutefois, on va conserver un lien entre la traduction et l’original, en précisant la langue des livres.
Ajouter deux colonnes translation_of et language pour gérer les traductions. translation_of doit être une foreign key d’un livre vers un autre livre. language contiendra le code de langue de 2 lettres suivant la norme ISO 639-1.
Ajouter au document Google Drive trois livres en langue étrangère que vous aimez, à la fois en version originale et en traduction française.
Si certains de vos livres déjà saisis sont des livres en langue étrangère, bien s’assurer que l’original et la traduction soient saisis.
Écrire une requête allant chercher tous les livres en anglais.
Écrire une requête allant chercher tous les livres pour lesquels il existe une traduction dans la base de données.
Écrire une requête déduisant la langue maternelle de chaque auteur. En effet, on peut considérer qu’un livre qui n’a pas de traduction est dans la langue maternelle de l’auteur.
Écrire une requête allant chercher les titres complets originaux & traduits et les noms d’auteurs complets de tous les livres pour lesquels il existe une traduction dans la base de données.
In [49]:
ALTER TABLE books ADD COLUMN translation_of int REFERENCES books(id);
ALTER TABLE books ADD COLUMN language varchar(2);
UPDATE books SET language = 'fr';
INSERT INTO books (title_prefix, title, author_id, language)
VALUES ('', 'Alice in Wonderland', 1, 'en'),
('', 'Through the Looking-Glass', 1, 'en'),
('The ', 'Pillars of the Earth', 2, 'en'),
('', 'World Without End', 2, 'en'),
('', 'Fall of Giants', 2, 'en');
UPDATE books SET translation_of = 6 WHERE id = 1;
UPDATE books SET translation_of = 7 WHERE id = 3;
UPDATE books SET translation_of = 8 WHERE id = 2;
UPDATE books SET translation_of = 9 WHERE id = 4;
UPDATE books SET translation_of = 10 WHERE id = 5;
In [50]:
SELECT
books.title_prefix || books.title AS original_title,
translated.title_prefix || translated.title AS translated_title,
authors.first_name || ' ' || authors.last_name AS author
FROM books
JOIN books AS translated
ON translated.translation_of = books.id
JOIN authors ON authors.id = books.author_id;
On a vu comment faire des statistiques simples en agrégant les données. Une agrégation simple permet d’avoir une donnée : une moyenne, une somme, etc.
Les statistiques complexes nécessitent d’avoir une succession d’agrégations de données.
Exemples de statistiques complexes : graphe, camembert, diagramme de Venn, diagramme de cordes.
Pour obtenir une succession d’agrégations de données, on doit grouper par une autre colonne ces agrégations. Par exemple, « le nombre de livres par langue » se traduit par « on souhaite compter le nombre de livres pour une langue donnée ». On doit donc faire une agrégation count. Et plus précisément, on doit « grouper par langue les livres, puis utiliser l’agrégation count pour connaître le nombre par langue ». On écrit donc :
In [51]:
SELECT language, count(books)
FROM books
GROUP BY language;
Écrire une requête renvoyant la moyenne de nombre de pages par langue.
De la requête précédente, on serait tenter de déduire quel langue est plus courte à écrire qu’une autre. Cependant, des livres non traduits peuvent exister dans la base de données, et perturber notre statistique. En effet, qu’un livre fasse 800 pages en anglais ne nous apprend rien sur la densité de l’anglais. Mais apprendre qu’un livre de 800 pages en anglais se traduit par 950 pages en français est plus parlant.
Écrire une requête renvoyant le pourcentage de caractères en plus en français par rapport à l’anglais, en n’utilisant que les livres pour lesquels on a les versions anglaises et françaises.
On exclut évidemment de la requête tous les livres pour lesquels une des deux versions (française ou anglaise) n’a pas de nombre de pages.
In [52]:
SELECT avg(books.pages) / avg(other.pages) FROM books
JOIN books AS other
ON (other.translation_of = books.id OR other.id = books.translation_of) AND other.language = 'fr'
WHERE books.language = 'en';
Attention : pour des raisons logiques, toutes les colonnes dans SELECT autres que des agrégations doivent forcément être dans GROUP BY. Ici, language est bien dans les deux. On pourrait avoir envie d’afficher le titre des livres en même temps qu’on affiche le nombre. Mais ça n’aurait pas de sens, car l’objectif du GROUP BY est de rassembler plusieurs lignes suivant une même donnée. On ne pourrait pas mettre sur une même ligne « fr » tous les titres de livres français.
In [53]:
SELECT language, count(books)
FROM books
GROUP BY language
ORDER BY language;
Tout l’intérêt des bases de données relationnelles est de pouvoir faire des statistiques en se servant des relations entre les données.
Comme précédemment, on groupe par une ou plusieurs colonnes tout en effectuant une agrégation. Toutefois, on y ajoute une jointure pour rassembler les tables liées.
Par exemple, pour connaître le nombre de livres pour chaque auteur, on fait :
In [54]:
SELECT first_name, last_name, count(books)
FROM authors
JOIN books ON books.author_id = authors.id
GROUP BY first_name, last_name
ORDER BY last_name;
In [55]:
SELECT
language,
EXTRACT(year FROM avg(age(coalesce(death, now()), birth))) AS average_age
FROM authors
JOIN books ON books.author_id = authors.id
WHERE translation_of IS NULL
GROUP BY language;
On peut afficher de nombreuses manières les statistiques extraites.
Généralement, on génère une image à l’aide d’un autre outil, en partant des statistiques renvoyées par la base de données. Toutefois, une base de données est si puissante qu’on peut écrire la logique pour afficher un graphique directement en SQL.
Voici comment réaliser un diagramme circulaire à partir de presque n’importe quelle statistique : https://sql.noripyt.com/58725bf9/1
C’est très moche et en pratique on ne fait jamais cela, mais cela montre bien que les données sorties par notre requête GROUP BY sont prêtes pour une représentation graphique. De nos jours, on préférera générer un fichier SVG à l’aide d’outils externes.
Pour l’instant, notre base de données est incapable de gérer plusieurs auteurs pour un même livre. C’est handicapant, car on omet systématiquement le traducteur. Bien entendu, c’est plus gênant encore pour les publications scientifiques et autres ouvrages à auteurs multiples.
Un mauvais réflexe consiste à ajouter une seconde colonne, author2_id, puis une troisième author3_id, etc. Cette technique a l’avantage d’être simple à construire, mais elle souffre de deux importantes failles :
authors.La solution propre consiste à créer une table intermédiaire entre books et authors. On l’appelera books_authors
Chaque ligne de books_authors lie un livre à un auteur. On peut mettre plusieurs lignes mentionnant le même livre, et plusieurs lignes mentionnant le même auteur. Par exemple :
| book_id | author_id |
|---|---|
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
| … | … |
Ici, Alice au pays des merveilles (1) est lié à deux auteurs, Lewis Carroll (1) et Henri Bué (3). De l’autre côté du miroir (2) est lié aussi à deux auteurs, Lewis Carroll (1) et Paul Gilson (4).
On peut aussi noter que Lewis Carroll (1) est donc lié à deux livres.
Comme plusieurs auteurs peuvent être liés à un seul livre, et que plusieurs livres peuvent être liés à un seul auteur, on appelle cette relation un many-to-many.
Dans le tableur Google Drive, créer une table many-to-many nommée book_authors liant les auteurs aux livres.
Y inscrire le lien entre livres et auteurs qui était stocké précedemment dans la colonne author_id des livres.
Supprimer la colonne author_id des livres du tableur Google Drive.
Créer dans le tableur Google Drive tous les traducteurs des livres dont on a déjà les traductions. Ajouter le lien entre les livres et ces traducteurs.
Réimporter les données dans NoriSQL avec cette nouvelle structure. Ne pas oublier de faire les changements de type de colonnes comme d’habitude.
Écrire une requête récupérant les livres et leurs auteurs (traducteurs inclus).
In [56]:
CREATE TABLE books_authors (book_id int REFERENCES books(id),
author_id int REFERENCES authors(id));
INSERT INTO authors VALUES (3, 'Jean', 'Rosenthal', '1923-1-1', NULL);
INSERT INTO books_authors VALUES (6, 1), (7, 1), (8, 2), (9, 2), (10, 2),
(1, 1), (3, 1), (2, 2), (4, 2), (5, 2), (2, 3);
ALTER TABLE books DROP COLUMN author_id;
In [57]:
SELECT books.*, authors.*
FROM books
JOIN books_authors ON books_authors.book_id = books.id
JOIN authors ON authors.id = books_authors.author_id
ORDER BY title, last_name;
La requête de l’exercice 43 renvoie plusieurs fois les mêmes résultats. En effet, s’il y a 3 auteurs pour un même livre, la jointure fera que le livre sera renvoyé une fois pour chaque auteur, donnant quelque chose du genre :
| Livre | Auteur |
|---|---|
| Alice au pays des merveilles | Lewis Carroll |
| Alice au pays des merveilles | Henri Bué |
Lorsque les résultats sont affichés sur un site Internet, les auteurs sont généralement regroupés par le langage de programmation du site (Python, PHP, Java, etc), mais restent ainsi en SQL.
Mais grâce à PostgreSQL, on peut rassembler ces données joliment à l’aide de l’agrégation STRING_AGG. Il faut y adjoindre un GROUP BY, car on ne souhaite pas agréger les données de la table en une seule donnée, mais bien en les agréger en une donnée par livre :
In [58]:
SELECT books.title_prefix || books.title AS book,
STRING_AGG(authors.first_name || ' ' || authors.last_name,
', ' ORDER BY last_name, first_name) AS authors
FROM books
JOIN books_authors ON books_authors.book_id = books.id
JOIN authors ON authors.id = books_authors.author_id
GROUP BY books.id
ORDER BY title;
Maintenant qu’on peut stocker plusieurs auteurs par livre, le problème est qu’ils sont à égalité : on ne peut pas différencier l’auteur du traducteur ! Ajouter dans la table books_authors un champ profession stockant ce qu’a fait la personne : 'author', 'translator', 'proofreader', etc.
Adapter la requête précédente pour qu’à côté de chaque nom d’auteur soit sa tâche entre parenthèses. Par exemple :
Ken Follett (author), Jean Rosenthal (translator).
Encore un souci, les auteurs restent ordonnés par nom de famille, or on préférerait avoir l’auteur d’abord.
Ajouter un champ de nombre entier position permettant de mettre 1 pour l’auteur principal, 2 pour l’auteur secondaire, etc.
Adapter la requête de l’exercice 44 pour que les auteurs soient bien ordonnés par ce nombre position.
Évidemment, ces deux nouvelles données, profession et position, deviennent très redondantes. Il serait mieux de les regrouper dans une table, car la position devra toujours la même pour une même task. De plus, cela permettra d’éviter les fautes d’orthographe.
Créer une table professions contenant les deux champs. Appeler name l’ancien champ profession. Bien penser à créer les clés étrangères.
Réécrire la requête de l’exercice 45 en utilisant cette nouvelle table de professions.
Faire une amélioration sensée de la base de données. Celle que vous voulez !
Il y a énormément de possibilités, quelques idées :
In [59]:
DROP TABLE IF EXISTS books_authors;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
Twitter : @NoriPytCom - GitHub : @BertrandBordage